using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SharpCore.Data;
using SharpCore.Extensions;
using SharpCore.Utilities;

namespace MMS.DAO
{
	public class MaterialDAO
	{
		#region Fields
        private UnitDAO unitDAO = new UnitDAO(Param.ConnectionString.Value);
		private string connectionStringName;

		#endregion

		#region Constructors

		public MaterialDAO(string connectionStringName)
		{
			ValidationUtility.ValidateArgument("connectionStringName", connectionStringName);

			this.connectionStringName = connectionStringName;
		}

		#endregion

		#region Methods

		/// <summary>
		/// Saves a record to the Material table.
		/// </summary>
		public void Insert(MaterialDTO material)
		{
			ValidationUtility.ValidateArgument("material", material);

			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", material.Id),
				new SqlParameter("@Name", material.Name),
				new SqlParameter("@IdUnit", material.IdUnit),
				new SqlParameter("@Description", material.Description),
				new SqlParameter("@PriceByDay", material.PriceByDay)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_MaterialInsert", parameters);
		}

		/// <summary>
		/// Updates a record in the Material table.
		/// </summary>
		public void Update(MaterialDTO material)
		{
			ValidationUtility.ValidateArgument("material", material);

			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", material.Id),
				new SqlParameter("@Name", material.Name),
				new SqlParameter("@IdUnit", material.IdUnit),
				new SqlParameter("@Description", material.Description),
				new SqlParameter("@PriceByDay", material.PriceByDay)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_MaterialUpdate", parameters);
		}

		/// <summary>
		/// Deletes a record from the sp_Material table by its primary key.
		/// </summary>
		public void Delete(string id)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_MaterialDelete", parameters);
		}

		/// <summary>
		/// Deletes a record from the Material table by a foreign key.
		/// </summary>
		public void DeleteAllByIdUnit(string idUnit)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdUnit", idUnit)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_MaterialDeleteAllByIdUnit", parameters);
		}

		/// <summary>
		/// Selects a single record from the Material table.
		/// </summary>
		public MaterialDTO Select(string id)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelect", parameters))
			{
				if (dataReader.Read())
				{
					return MapDataReader(dataReader);
				}
				else
				{
					return null;
				}
			}
		}

		/// <summary>
		/// Selects a single record from the Material table.
		/// </summary>
		public string SelectJson(string id)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

			return SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelect", parameters);
		}

		/// <summary>
		/// Selects all records from the Material table.
		/// </summary>
		public List<MaterialDTO> SelectAll()
		{
			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectAll"))
			{
				List<MaterialDTO> materialDTOList = new List<MaterialDTO>();
				while (dataReader.Read())
				{
					MaterialDTO materialDTO = MapDataReader(dataReader);
					materialDTOList.Add(materialDTO);
				}

				return materialDTOList;
			}
		}

		/// <summary>
		/// Selects all records from the Material table.
		/// </summary>
		public string SelectAllJson()
		{
			return SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectAll");
		}

		/// <summary>
		/// Selects all records from the Material table by a foreign key.
		/// </summary>
		public List<MaterialDTO> SelectAllByIdUnit(string idUnit)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdUnit", idUnit)
			};

			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectAllByIdUnit", parameters))
			{
				List<MaterialDTO> materialDTOList = new List<MaterialDTO>();
				while (dataReader.Read())
				{
					MaterialDTO materialDTO = MapDataReader(dataReader);
					materialDTOList.Add(materialDTO);
				}

				return materialDTOList;
			}
		}

		/// <summary>
		/// Selects all records from the Material table by a foreign key.
		/// </summary>
		public string SelectAllByIdUnitJson(string idUnit)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdUnit", idUnit)
			};

			return SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectAllByIdUnit", parameters);
		}

		/// <summary>
		/// Creates a new instance of the MaterialDTO class and populates it with data from the specified SqlDataReader.
		/// </summary>
		private MaterialDTO MapDataReader(SqlDataReader dataReader)
		{
			MaterialDTO materialDTO = new MaterialDTO();
			materialDTO.Id = dataReader.GetString("Id", String.Empty);
			materialDTO.Name = dataReader.GetString("Name", null);
			materialDTO.IdUnit = dataReader.GetString("IdUnit", String.Empty);
			materialDTO.Description = dataReader.GetString("Description", null);
			materialDTO.PriceByDay = dataReader.GetInt32("PriceByDay", 0);
            materialDTO.UnitName = dataReader.GetString("UnitName", String.Empty);

			return materialDTO;
		}

        /// <summary>
        /// Creates a new instance of the MaterialDTO class and populates it with data from the specified SqlDataReader.
        /// </summary>
        private MaterialDTO MapDataReader2(SqlDataReader dataReader)
        {
            MaterialDTO materialDTO = new MaterialDTO();
            materialDTO.Id = dataReader.GetString("Id", String.Empty);
            materialDTO.Name = dataReader.GetString("Name", null);
            materialDTO.IdUnit = dataReader.GetString("IdUnit", String.Empty);
            materialDTO.Description = dataReader.GetString("Description", null);
            materialDTO.PriceByDay = dataReader.GetInt32("PriceByDay", 0);
            materialDTO.UnitName = dataReader.GetString("UnitName", String.Empty);
            materialDTO.Remaining = dataReader.GetInt32("Remaining", 0);


            return materialDTO;
        }
		
        public List<MaterialDTO> SelectByRepository(string repositoryId)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@RepositoryId", repositoryId)
			};

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectByRepository", parameters))
            {
                List<MaterialDTO> materialDTOList = new List<MaterialDTO>();
                while (dataReader.Read())
                {
                    MaterialDTO materialDTO = MapDataReader2(dataReader);
                    materialDTOList.Add(materialDTO);
                }

                return materialDTOList;
            }
        }
		
        internal MaterialDTO selectByIdUnit(string idUnit)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdUnit", idUnit)
			};

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_MaterialSelectByIdUnit", parameters))
            {
                if (dataReader.Read())
                {
                    return MapDataReader(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }
		#endregion
    }
}
